package jnpf.database.sql.enums;

import jnpf.database.constant.DbAliasConst;
import jnpf.database.datatype.db.interfaces.DtInterface;
import jnpf.database.enums.DbAliasEnum;
import jnpf.database.enums.ParamEnum;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.base.SqlFrameBase;
import jnpf.database.sql.model.DbStruct;
import lombok.AllArgsConstructor;
import lombok.Getter;

import java.util.List;

/**
 * SqlServer SQL语句模板
 *
 * @author JNPF开发平台组 YanYu
 * @version V3.3
 * @copyright 引迈信息技术有限公司
 * @date 2022/3/30
 */
@Getter
@AllArgsConstructor
public enum SqlSQLServerEnum implements SqlFrameBase{

    /* =============================== 系统语句 ==================================== */
    FIELDS("SELECT cast(a.name as varchar(50)) "+ DbAliasConst.FIELD+" ," +
            " cast(case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( " +
            " SELECT name FROM sysindexes WHERE indid in( " +
            " SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) " +
            " then '1' else '0' end as varchar(50)) "+DbAliasConst.PRIMARY_KEY+", " +
            " cast(b.name as varchar(50)) "+DbAliasConst.DATA_TYPE+", " +
            " cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') as varchar(50)) " + DbAliasConst.DATA_LENGTH + ", " +
            " a.length " + DtInterface.CHARACTER_LENGTH + ", " +
            " a.xprec " + DtInterface.NUMERIC_PRECISION + ", " +
            " a.xscale " + DtInterface.NUMERIC_SCALE + ", " +
            " cast(case when a.isnullable=0 then '0'else '1' end as varchar(50)) "+ DbAliasEnum.ALLOW_NULL.AS() +", " +
            " cast(isnull(e.text,'') as varchar(50)) "+DbAliasConst.DEFAULT_VALUE+", " +
            " cast(isnull(g.[value],'') as varchar(50)) " +DbAliasConst.FIELD_COMMENT+
            "\nFROM syscolumns a " +
            "left join systypes b on a.xusertype=b.xusertype " +
            "inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' " +
            "left join syscomments e on a.cdefault=e.id " +
            "left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id " +
            "left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 " +
            "where d.name = " + ParamEnum.DB_NAME.getParamSign() +
            "\norder by a.id,a.colorder"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
        }
    },
    TABLES("SET NOCOUNT ON DECLARE @TABLEINFO TABLE ( NAME VARCHAR(50) ," +
            " SUMROWS VARCHAR(11) , RESERVED VARCHAR(50) , DATA VARCHAR(50) ," +
            " INDEX_SIZE VARCHAR(50) , UNUSED VARCHAR(50) , PK VARCHAR(50) )" +
            " DECLARE @TABLENAME TABLE ( NAME VARCHAR(50) ) DECLARE @NAME VARCHAR(50)" +
            " DECLARE @PK VARCHAR(50) INSERT INTO @TABLENAME ( NAME )" +
            " SELECT O.NAME FROM SYSOBJECTS O , SYSINDEXES I " +
            "WHERE O.ID = I.ID AND O.XTYPE = 'U' AND I.INDID < 2 ORDER BY I.ROWS DESC , O.NAME" +
            " WHILE EXISTS ( SELECT 1 FROM @TABLENAME ) BEGIN SELECT TOP 1 " +
            "@NAME = NAME FROM @TABLENAME DELETE @TABLENAME WHERE NAME = " +
            "@NAME DECLARE @OBJECTID INT SET @OBJECTID = OBJECT_ID(@NAME) SELECT @PK = " +
            "COL_NAME(@OBJECTID, COLID) FROM SYSOBJECTS AS O INNER JOIN SYSINDEXES AS I ON I.NAME = " +
            "O.NAME INNER JOIN SYSINDEXKEYS AS K ON K.INDID = I.INDID WHERE O.XTYPE = " +
            "'PK' AND PARENT_OBJ = @OBJECTID AND K.ID = @OBJECTID INSERT INTO " +
            "@TABLEINFO ( NAME , SUMROWS , RESERVED , DATA , INDEX_SIZE , UNUSED ) EXEC SYS.SP_SPACEUSED " +
            "@NAME UPDATE @TABLEINFO SET PK = @PK WHERE NAME = @NAME END SELECT cast(F.NAME AS varchar(50))" +
            DbAliasConst.TABLE_NAME + ",cast(ISNULL( P.TDESCRIPTION, F.NAME )  AS varchar(50)) "+
            DbAliasConst.TABLE_COMMENT + ",cast(F.RESERVED AS varchar(50))" +
            " F_SIZE,cast(RTRIM( F.SUMROWS ) AS varchar(50)) " + DbAliasConst.TABLE_SUM + ",cast(F.PK AS varchar(50)) " +
            "F_PRIMARYKEY FROM @TABLEINFO F LEFT JOIN ( SELECT NAME = CASE WHEN A.COLORDER = " +
            "1 THEN D.NAME ELSE '' END , TDESCRIPTION = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '')" +
            " ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D" +
            " ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID =" +
            " F.MAJOR_ID WHERE A.COLORDER = 1 AND F.MINOR_ID = 0 ) P ON F.NAME = P.NAME WHERE 1 = 1 ORDER BY " + DbAliasConst.TABLE_NAME
    ),
    TABLE(
            TABLES.sqlFrame.replace("1 = 1", "F.NAME = " + ParamEnum.TABLE.getParamSign())
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
        }
    },
    EXISTS_TABLE(
            "SELECT COUNT (*) AS TOTAL FROM (" +
            "SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_type = 'BASE TABLE' and TABLE_NAME = "+ParamEnum.TABLE.getParamSign()
            + ") AS COUNT_TAB"
    ){
        @Override
        public void setStructParams(String table, DbStruct dbStruct, List<String> list) {
            list.add(table);
        }
    },

    /* =============================== 定义语句 ==================================== */
    CREATE_TABLE(SqlDMEnum.CREATE_TABLE.getSqlFrame()){
        @Override
        public String sqlSpecial(String sqlFrame, List<String> frameParamList, List<String> paramList) {
            return SqlDMEnum.CREATE_TABLE.sqlSpecial(sqlFrame, frameParamList, paramList);
        }
    },
    // （Data Definition Language）简称 DDL：用来建立数据库、数据库对象和定义列的命令。包括：create、alter、drop
    RE_TABLE_NAME   ("EXEC sp_rename {oldTable}, {newTable}"),


    COMMENT_TABLE   ("EXEC sp_addextendedproperty 'MS_Description',N'{comment}','SCHEMA',N'dbo','TABLE',N'{table}'"),
    COMMENT_COLUMN  ("EXEC sp_addextendedproperty 'MS_Description',N'{comment}','SCHEMA',N'dbo','TABLE',N'{table}','COLUMN',N'{column}'"),
    ALTER_COLUMN    ("<ALTER> <TABLE> {table} <ADD> {column} {dataType} [[NOT] [NULL]] [<DEFAULT> {defaultValue}]"),

    /* =============================== DML操作语句 ==================================== */
    // （Data Manipulation Language）简称 DML：用来操纵数据库中数据的命令。包括：select、insert、update、delete。

    /*第二种方式：offset fetch next方式（SQL2012以上的版本才支持：推荐使用 ）-*/
    SELECT_PAGE_NEW ("{selectSql} [orderSql] OFFSET {beginIndex} rows fetch next {pageSize} rows only"),
    /*
            row_number() over(order by {column})
            row_number() over(order by RAND())      随机字段
            row_number() over(order by NEWID())     临时ID
     */
    ORDER_PAGE     ("SELECT * " +
                            "FROM " +
                                "(SELECT TOP {endIndex} ROW_NUMBER() OVER(ORDER BY {orderColumn}) JNPF_ROW,* " +
                            "FROM " +
                                "({selectSql}) AS JNPF_TAB ORDER BY JNPF_ROW) AS JNPF_TEMP_TABLE " +
                            "WHERE " +
                                "JNPF_ROW BETWEEN {beginIndex} AND {endIndex}"),
    DB_TIME_SQL     ("Select CONVERT(varchar(100), GETDATE(), 120) as TIME"),



    /* =============================== 其他 ==================================== */



    ;

    public String sqlFrame;
    private final String dbEncode = DbBase.SQL_SERVER;

}
